<?php
//======================================================================================
//
// Function: REEFTintegrationLog - Get customers
//
// Programmer: JKJ
// Date      : 2025-05-27
//
// ChatGPT Conversaion
// https://chatgpt.com/share/680e7c97-6c40-8012-83cd-aad9c3ba8ec3
//
// FTS5 (Full-Text Search)
// https://chatgpt.com/share/6811f5bd-5134-8012-bbbf-afaf8a5b115b
//
// Copyright Reeft A/S (c) - 2025
//======================================================================================

//======================================================================================
// General config
//======================================================================================
	include "config/config.php";

//======================================================================================
// Set language
//======================================================================================

	include "include/set_language.php";

//======================================================================================
// Get input
//======================================================================================

	if (isset($_REQUEST["customer"])) {
		$customer = $_REQUEST["customer"];
	} else {
		$customer = '';
	}

	if (isset($_REQUEST["group_search"])) {
		$group_search = $_REQUEST["group_search"];
	} else {
		$group_search = '';
	}

	if (isset($_REQUEST["active_search"])) {
		$active_search = $_REQUEST["active_search"];
	} else {
		$active_search = '';
	}

	if (isset($_REQUEST["search_arg"])) {
		$search_arg = $_REQUEST["search_arg"];
	} else {
		$search_arg = '';
	}

	//===============================================================
	// Pagination fields "local"/datatables
	//===============================================================

	if (isset($_REQUEST["rpyOffSet"])) {
		$rpyOffSet = $_REQUEST["rpyOffSet"];
	} else {
		$rpyOffSet = 0;
	}

	if (isset($_REQUEST["rpyPageSize"])) {
		$rpyPageSize = $_REQUEST["rpyPageSize"];
	} else {
		$rpyPageSize = 0;
	}

	if (isset($_REQUEST["rpyOrderColumn"])) {
		$rpyOrderColumn = $_REQUEST["rpyOrderColumn"];
	} else {
		$rpyOrderColumn = '';
	}

//======================================================================================
// Get order by
//======================================================================================
	$rpyOrderColumn = str_replace("|", ' ', $rpyOrderColumn);
	$aryOrderBy = explode(',', $rpyOrderColumn);


//======================================================================================
// Set database
//======================================================================================

	$db_name = $CUSTOMER_DATABASE_PATH;
	//$db_name = 'customer/REEFT_integration.sqlite3';
	
//======================================================================================
// Start me up...
//======================================================================================
	$startTime = microtime(true);

//======================================================================================
// Check if database file exists
//======================================================================================
	if (!file_exists($db_name)) {
		die(json_encode(['error' => "Database file '$db_name' does not exist."]));
	}

//======================================================================================
// Connect to some DB
//======================================================================================

	$DFT_SQLLITE_IP	= $db_name;
	include "include/db_connect.php";


//======================================================================================
// Create where statement
//======================================================================================

	$sql_where = '';
	
	// Search argument
	if ( $search_arg <> '' ) {
		$sql_where = "WHERE (cust_number || ' ' || cust_name || ' ' || ERPname || ' ' || ERPdescription) LIKE '%$search_arg%' ";
	}
	
	// What active to show
	// $show_activ_only = '0';
	// $show_activ_only = '1';
	// $show_activ_only = '';
	$show_activ_only = $active_search;

	if ( $show_activ_only == '1' ) {
		$sql_active_where 	= 'where cust_active = 1';
		$sql_active_and		= 'and cust_active = 1';
	}
	if ( $show_activ_only == '0' ) {
		$sql_active_where 	= 'where cust_active = 0';
		$sql_active_and		= 'and cust_active = 0';
	}
	if ( $show_activ_only == '' ) {
		$sql_active_where 	= '';
		$sql_active_and		= '';
	}

	
	// Group search
	if ( $group_search <> '' ) {
		
		$sql_group_search_where 	= "where cust_group = $group_search";
		$sql_group_search_and	 	= "and cust_group = $group_search";
		
	} else {
		
		$sql_group_search_where 	= '';
		$sql_group_search_and	 	= '';
		
	}
//======================================================================================
// Decide if you want to use a where or and, depending of the content of $sql_where
//======================================================================================
	if ( $sql_where == '' ) {
		$sql_where = $sql_active_where;
	} else {
		$sql_where = $sql_where . ' ' . $sql_active_and;
	}
	
	if ( $sql_where == '' ) {
		$sql_where = $sql_group_search_where;
	} else {
		$sql_where = $sql_where . ' ' . $sql_group_search_and;
	}
	
//======================================================================================
// Get groups
//======================================================================================

	$data_group		= [];
	
	if ( $active_search <> '' ) {
		$group_where_active = 'where cust_active = ' . $active_search;
	} else {
		$group_where_active = '';
	}

	$sql =  "SELECT cust_group, count(*) as cust_group_count FROM reeft_customer $group_where_active GROUP by cust_group";
	include "include/db_run_sql.php";
	
	// Loop result
	foreach ($data as $row) {

		$data_group[] = $row;
	}
	
//======================================================================================
// Get number of records
//======================================================================================

	$recordsTotal = 0;

	$sql =  "SELECT tableCount as recordsTotal FROM recordCounter WHERE tableName = 'reeft_customer'";
	include "include/db_run_sql.php";

	foreach( $data as $row )
	{
		$recordsTotal = $row["recordsTotal"];
	}

	// Make integer
	$recordsTotal = intval( $recordsTotal );

	// Format it...
	$recordsTotalFormat = number_format($recordsTotal,0,",",".");

//======================================================================================
// Get number of records filtered
//======================================================================================

	$recordsTotalFiltered = 0;


	$sql = "SELECT count(*) as recordsTotalFiltered FROM reeft_customer
			$sql_where
			";

	$sql_filter = $sql;

	include "include/db_run_sql.php";

	foreach( $data as $row )
	{
		$recordsTotalFiltered = $row["recordsTotalFiltered"];
	}

	// Make integer
	$recordsTotalFiltered = intval( $recordsTotalFiltered );

	// Format it...
	$recordsTotalFiltered = number_format($recordsTotalFiltered,0,",",".");

//======================================================================================
// Fetch all customer rows
//======================================================================================
	$data_data		= [];
	$total_found 	= 0;
	$total_notfound = 0;

	// Set SQL and execute statement
	// $query 	= "SELECT * FROM reeft_customer LIMIT $rpyPageSize OFFSET $rpyOffSet";
	// $rows 	= $db->query($query)->fetchAll(PDO::FETCH_ASSOC);

//======================================================================================

	$sql = "SELECT * FROM reeft_customer
			$sql_where
			ORDER BY $rpyOrderColumn
			LIMIT $rpyPageSize OFFSET $rpyOffSet
			";
	include "include/db_run_sql.php";

	//print_r($data);

	// Loop result
	foreach ($data as $row) {

		// Count active/inactive
		if ((int)$row['cust_active'] === 1) {
			$total_found++;
		} else {
			$total_notfound++;
		}

		// Add useful fields
		$row['name'] 	= $row['cust_name'];
		$row['group'] 	= (int)$row['cust_group'];

		$data_data[] = $row;

	}

	// print_r($row);
	// print_r($data);

//======================================================================================
// Make SQL pretty
//======================================================================================

	$sql_pretty 		= str_replace("\r\n", ' ', $sql); 				// remove carriage returns
	$sql_pretty 		= str_replace("\t", ' ', $sql_pretty); 			// remove carriage returns

	$sql_filter_pretty 	= str_replace("\r\n", ' ', $sql_filter); 		// remove carriage returns
	$sql_filter_pretty 	= str_replace("\t", ' ', $sql_filter_pretty); 	// remove carriage returns

	$sql_where_pretty 	= str_replace("\r\n", ' ', $sql_where); 		// remove carriage returns
	$sql_where_pretty 	= str_replace("\t", ' ', $sql_where_pretty); 	// remove carriage returns

//======================================================================================
// That's all folks...
//======================================================================================
	$executionTime = microtime(true) - $startTime;

//======================================================================================
// Build response
//======================================================================================
	$response = [
		'header' => [
			'db_name' 				=> $db_name,
			'sql' 					=> $sql_pretty,
			'sql_filter' 			=> $sql_filter_pretty,
			'sql_where' 			=> $sql_where_pretty,
			'search_arg' 			=> $search_arg,
			'group_search' 			=> $group_search,
			'active_search' 		=> $active_search,
			'rpyPageSize' 			=> $rpyPageSize,
			'rpyOffSet' 			=> $rpyOffSet,
			'rpyOrderColumn' 		=> $rpyOrderColumn,
			'total_found' 			=> $total_found,
			'total_notfound' 		=> $total_notfound,
			'total_total' 			=> $recordsTotal,
			'execution_time_sec'	=> number_format($executionTime, 6),
			'execution_time_ms' 	=> number_format($executionTime * 1000, 6)
		],
		'recordsTotal' 				=> $recordsTotal,
		'recordsFiltered' 			=> $recordsTotalFiltered,
		'data' 						=> $data_data,
		'data_group'				=> $data_group,
		'errors' 					=> [] // placeholder
	];

	// Output as JSON
	header('Content-Type: application/json');
	echo json_encode($response, JSON_PRETTY_PRINT);

?>